package vn.edu.saigontech.ncms.web.offering;

import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;

import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.program.Level;
import vn.edu.saigontech.ncms.model.program.ProgramType;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class ExportAction extends BaseAction {
	
	private Long id;
	private Long[] ids;
	
	public String doExportCambridgeTestFormToExcel() {
		Offering offering = OfferingService.INSTANCE.findById(id);
		if (Checker.isNull(offering)) return NONE;
		ProgramType type = offering.getLevel().getProgram().getProgramType();
		Level level = offering.getLevel();
		HSSFWorkbook wb = null;
		
		if (type != null && type != ProgramType.CHILDREN) return NONE;
		else {
			if (level.getName().startsWith("Starter"))
				wb = ExcelUtil.getTemplate("cambridge-children-starter.xls");
			else if (level.getName().startsWith("Flyer"))
				wb = ExcelUtil.getTemplate("cambridge-children-flyer.xls");
			else if (level.getName().startsWith("Mover"))
				wb = ExcelUtil.getTemplate("cambridge-children-mover.xls");
			else { return NONE; }
			
			Map<String, CellStyle> styles = createStyles(wb);
			if (Checker.isNull(wb)) return NONE;
			ExcelUtil.setDefaultFont(wb, "Times New Roman", (short)11);

			/* BEGIN WRITING DATA (SPEAKING SHEET) */
			HSSFSheet sheet = wb.getSheetAt(0);
			HSSFRow row = sheet.getRow(1);
			int numberOfCols = row.getLastCellNum();
			ExcelUtil.makeStringCell(row, 1, "Class: " + offering.getName(), styles, "title");
			ExcelUtil.makeStringCell(row, 6, "Test Date: " + DateUtil.toString(offering.getCambridgeSpokenDate(), "MMM dd, yyyy"), styles, "title");
			
			int cRow = 7;
			generateCells(cRow, offering, sheet, styles, numberOfCols);
			
			/* BEGIN WRITING DATA (TOTAL SHEET) */
			sheet = wb.getSheetAt(1);
			row = sheet.getRow(0);
			numberOfCols = row.getLastCellNum();
			row = sheet.getRow(1);
			ExcelUtil.makeStringCell(row, 1, "Class: " + offering.getName(), styles, "title");
			ExcelUtil.makeStringCell(row, 6, "Date: " + DateUtil.toString(offering.getCambridgeSpokenDate(), "MMM dd, yyyy"), styles, "title");
			
			cRow = 6;
			generateCells(cRow, offering, sheet, styles, numberOfCols);
		}
			
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Cambridge Test Form Class " + offering.getName() + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.offering.export.cambridge", offering.getName());
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	public String doExportSpeakingTestFormToExcel() throws IOException {
		Offering offering = OfferingService.INSTANCE.findById(id);
		if (Checker.isNull(offering)) return NONE;
		ProgramType type = offering.getLevel().getProgram().getProgramType();
		Level level = offering.getLevel();		
		HSSFWorkbook wb = null;
		
		if (type != null && (type == ProgramType.GE || type == ProgramType.TEENAGER)) {
			wb = ExcelUtil.getTemplate("speaking-test-general.xls");
			Map<String, CellStyle> styles = createStyles(wb);
			if (Checker.isNull(wb)) return NONE;
			
			ExcelUtil.setDefaultFont(wb, "Times New Roman", (short)11);
			HSSFSheet sheet = wb.getSheetAt(0);
	
			/* BEGIN WRITING DATA */ 
			HSSFRow row = sheet.getRow(0);
			int numberOfCols = row.getLastCellNum();
			ExcelUtil.makeStringCell(row, 1, "Class: " + offering.getName(), styles, "title");
			row = sheet.getRow(1);
			ExcelUtil.makeStringCell(row, 1, "Test Date: " + DateUtil.toString(offering.getFinalSpokenDate(), "MMM dd, yyyy"), styles, "title");
			ExcelUtil.makeStringCell(row, 4, "Speaking - " + offering.getLevel().getProgram().getName() + " - Level " + offering.getLevel().getName(), styles, "cTitle");
			row = sheet.getRow(2);
			ExcelUtil.makeStringCell(row, 1, "Intructor: ______________________", styles, "title");
			
			int cRow = 6;
			int nextRow = generateCells(cRow, offering, sheet, styles, numberOfCols);
			
			row = sheet.createRow(nextRow);
			ExcelUtil.makeStringCell(row, numberOfCols - 2, "Tester's Signature", styles, "cTitle");
		}
		
		else if (type != null && (type == ProgramType.CHILDREN)) {
			if (level.getName().startsWith("Starter"))
				wb = ExcelUtil.getTemplate("speaking-children-starter.xls");
			else if (level.getName().startsWith("Flyer"))
				wb = ExcelUtil.getTemplate("speaking-children-flyer.xls");
			else if (level.getName().startsWith("Mover"))
				wb = ExcelUtil.getTemplate("speaking-children-mover.xls");
			else { return NONE; }
			
			Map<String, CellStyle> styles = createStyles(wb);
			if (Checker.isNull(wb)) return NONE;
			
			ExcelUtil.setDefaultFont(wb, "Times New Roman", (short)11);
			HSSFSheet sheet = wb.getSheetAt(0);
	
			/* BEGIN WRITING DATA */ 
			HSSFRow row = sheet.getRow(1);
			int numberOfCols = row.getLastCellNum();
			ExcelUtil.makeStringCell(row, 1, "Class: " + offering.getName(), styles, "title");
			ExcelUtil.makeStringCell(row, 6, "Test Date: " + DateUtil.toString(offering.getFinalSpokenDate(), "MMM dd, yyyy"), styles, "title");
			
			int cRow = 7;
			generateCells(cRow, offering, sheet, styles, numberOfCols);
		}
			
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Speaking Test Form Class " + offering.getName() + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.offering.export.gradeSheet", offering.getName());
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	public Long getId() {
		return id;
	}

	public void setId(Long id) {
		this.id = id;
	}

	public Long[] getIds() {
		return ids;
	}

	public void setIds(Long[] ids) {
		this.ids = ids;
	}
	
	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font cTitleFont = wb.createFont();
        cTitleFont.setFontHeightInPoints((short)13);
        cTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(cTitleFont);
        styles.put("cTitle", style);

        Font bFont = wb.createFont();
        bFont.setFontHeightInPoints((short)12);
        bFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(bFont);
        styles.put("bHeading", style);
        
        Font nFont = wb.createFont();
        nFont.setFontHeightInPoints((short)12);
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setFont(nFont);
        styles.put("heading", style);
        
        Font noFont = wb.createFont();
        noFont.setFontHeightInPoints((short)12);
        style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setFont(noFont);
        styles.put("no", style);
        
        style = wb.createCellStyle();
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(nFont);
        styles.put("lastName", style);

        style = wb.createCellStyle();
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(nFont);
        styles.put("firstName", style);
        
        style = wb.createCellStyle();
        Font topFont = wb.createFont();
        topFont.setFontHeightInPoints((short)14);
        topFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setFont(topFont);
        style.setAlignment(CellStyle.ALIGN_CENTER);
        styles.put("topTitle", style);
        
        return styles;
    }
	
	/**
	 *  generate the cells for students list 
	 */
	private int generateCells(int cRow, Offering offering, HSSFSheet sheet, Map<String, CellStyle> styles, int numberOfCols) {
		int count = 1;
		
		for (OfferingMember member : offering.getSortActiveMembers()) {
			HSSFRow row = sheet.createRow(cRow++);
			
			ExcelUtil.makeNumericCell(row, 0, count++, styles, "no");
			ExcelUtil.makeStringCell(row, 1, member.getReceipt().getStudent().getLastName(), styles, "lastName");
			ExcelUtil.makeStringCell(row, 2, member.getReceipt().getStudent().getFirstName(), styles, "firstName");
			ExcelUtil.makeStringCell(row, 3, member.getReceipt().getStudent().getNickName(), styles, "heading");
			
			for (int i = 4; i < numberOfCols; i++)
				ExcelUtil.makeStringCell(row, i, "", styles, "heading");
		}
		return cRow;
	}
}